unit clsTabelaPreco;

interface

uses Dm, dbtables, biblio, clsparametros;

type
  TTabelaPreco = class(TObject)
  private
    Tab_codigo: string;
  public
    Tab_descricao: string;
    Tab_coluna: string;
    Tab_Vig_Inicio: TDateTime;
    Tab_Vig_Termino: TDateTime;
    Tab_Desc1: double;
    Tab_Desc2: double;
    Tab_Desc3: double;
    Tab_Desc4: double;
    Tab_Desc5: double;
    Tab_Desc6: double;
    Tab_Desc7: double;
    Tab_Desc8: double;
    Tab_Desc9: double;
    Tab_Desc10: double;
    Tab_JurosMes: double;
    TAB_COMISSAO: double;
    Tab_Desc_Redespacho: string;
    Cod_AtribC: string;
    function Gravar(p_Tab_codigo: string): Byte;
    function Recuperar(p_Tab_codigo: string): Byte;
    function Recuperar_Vigente(data: TDateTime): string;
    function Excluir(p_Tab_codigo: string): Byte;
    procedure Consultar(var p_q: TQuery; p_nome: string);
  end;

implementation

uses DB;

function TTabelaPreco.Gravar;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DmInt);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('SELECT * FROM TabelaPreco where Tab_codigo=:k_Tab_codigo');
  tq.parambyname('k_Tab_codigo').asstring := strzero(p_Tab_codigo, 3);
  tq.open;
  if tq.eof then begin
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('INSERT INTO TabelaPreco VALUES(' +
      ':k_Tab_codigo,' +
      ':k_Tab_descricao,' +
      ':K_Tab_coluna,' +
      ':k_Tab_Vig_Inicio,' +
      ':k_Tab_Vig_Termino,' +
      ':k_Tab_Desc1,' +
      ':k_Tab_Desc2,' +
      ':k_Tab_Desc3,' +
      ':k_Tab_Desc4,' +
      ':k_Tab_Desc5,' +
      ':k_Tab_Desc6,' +
      ':k_Tab_Desc7,' +
      ':k_Tab_Desc8,' +
      ':k_Tab_Desc9,' +
      ':k_Tab_Desc10,' +
      ':k_Tab_JurosMes,' +
      ':k_TAB_COMISSAO,' +
      ':k_Tab_Desc_Redespacho ');
    if Cod_AtribC <> '' then
      tq.sql.add(',:k_Cod_AtribC)')
    else
      tq.sql.add(',null)');
  end else begin
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('UPDATE TabelaPreco SET ' +
      'Tab_descricao  =:k_Tab_descricao,' +
      'Tab_coluna     =:K_Tab_Coluna,' +
      'Tab_Vig_Inicio =:k_Tab_Vig_Inicio,' +
      'Tab_Vig_Termino=:k_Tab_Vig_Termino,' +
      'Tab_Desc1      =:k_Tab_Desc1,' +
      'Tab_Desc2      =:k_Tab_Desc2,' +
      'Tab_Desc3      =:k_Tab_Desc3,' +
      'Tab_Desc4      =:k_Tab_Desc4,' +
      'Tab_Desc5      =:k_Tab_Desc5,' +
      'Tab_Desc6      =:k_Tab_Desc6,' +
      'Tab_Desc7      =:k_Tab_Desc7,' +
      'Tab_Desc8      =:k_Tab_Desc8,' +
      'Tab_Desc9      =:k_Tab_Desc9,' +
      'Tab_Desc10     =:k_Tab_Desc10,' +
      'Tab_JurosMes   =:k_Tab_JurosMes,' +
      'TAB_COMISSAO   =:k_TAB_COMISSAO,' +
      'Tab_Desc_Redespacho =:k_Tab_Desc_Redespacho,');
    if Cod_AtribC <> '' then
      tq.sql.add('Cod_AtribC    =:k_Cod_AtribC ')
    else
      tq.sql.add('Cod_AtribC    =null ');
    tq.sql.add('WHERE Tab_codigo=:k_Tab_codigo');
  end;
  tq.parambyname('k_Tab_codigo').asstring := strzero(p_Tab_codigo, 3);
  tq.parambyname('k_Tab_descricao').asstring := Tab_descricao;
  tq.parambyname('k_Tab_Coluna').asstring := Tab_coluna;
  tq.parambyname('k_Tab_Vig_Inicio').asdatetime := Tab_Vig_Inicio;
  tq.parambyname('k_Tab_Vig_Termino').asdatetime := Tab_Vig_Termino;
  tq.parambyname('k_Tab_Desc1').asfloat := Tab_Desc1;
  tq.parambyname('k_Tab_Desc2').asfloat := Tab_Desc2;
  tq.parambyname('k_Tab_Desc3').asfloat := Tab_Desc3;
  tq.parambyname('k_Tab_Desc4').asfloat := Tab_Desc4;
  tq.parambyname('k_Tab_Desc5').asfloat := Tab_Desc5;
  tq.parambyname('k_Tab_Desc6').asfloat := Tab_Desc6;
  tq.parambyname('k_Tab_Desc7').asfloat := Tab_Desc7;
  tq.parambyname('k_Tab_Desc8').asfloat := Tab_Desc8;
  tq.parambyname('k_Tab_Desc9').asfloat := Tab_Desc9;
  tq.parambyname('k_Tab_Desc10').asfloat := Tab_Desc10;
  tq.parambyname('k_Tab_JurosMes').asfloat := Tab_JurosMes;
  tq.ParamByName('k_TAB_COMISSAO').AsFloat := TAB_COMISSAO;
  tq.ParamByName('k_Tab_Desc_Redespacho').AsString := Tab_Desc_Redespacho;
  if Cod_AtribC <> '' then
    tq.ParamByName('k_Cod_AtribC').AsString := Cod_AtribC;
  tq.close;
  tq.execsql;
  result := 1;
  tq.close;
  tq.Free;
end;

function TTabelaPreco.Recuperar;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.sql.add('SELECT * FROM TabelaPreco where Tab_codigo=:k_Tab_codigo');
  tq.Params[0].asstring := strzero(p_Tab_codigo, 3);
  tq.open;
  Tab_codigo := p_Tab_codigo;
  if tq.eof then result := 0 else result := 1;
  Tab_descricao := tq.fieldbyname('Tab_descricao').asstring;
  Tab_coluna := tq.fieldbyname('Tab_coluna').asstring;
  Tab_Vig_Inicio := tq.fieldbyname('Tab_Vig_Inicio').asdatetime;
  Tab_Vig_Termino := tq.fieldbyname('Tab_Vig_Termino').asdatetime;
  Tab_Desc1 := tq.fieldbyname('Tab_Desc1').asfloat;
  Tab_Desc2 := tq.fieldbyname('Tab_Desc2').asfloat;
  Tab_Desc3 := tq.fieldbyname('Tab_Desc3').asfloat;
  Tab_Desc4 := tq.fieldbyname('Tab_Desc4').asfloat;
  Tab_Desc5 := tq.fieldbyname('Tab_Desc5').asfloat;
  Tab_Desc6 := tq.fieldbyname('Tab_Desc6').asfloat;
  Tab_Desc7 := tq.fieldbyname('Tab_Desc7').asfloat;
  Tab_Desc8 := tq.fieldbyname('Tab_Desc8').asfloat;
  Tab_Desc9 := tq.fieldbyname('Tab_Desc9').asfloat;
  Tab_Desc10 := tq.fieldbyname('Tab_Desc10').asfloat;
  Tab_JurosMes := tq.fieldbyname('Tab_JurosMes').asfloat;
  TAB_COMISSAO := tq.fieldbyname('TAB_COMISSAO').AsFloat;
  Tab_Desc_Redespacho := tq.fieldbyname('Tab_Desc_Redespacho').AsString;
  Cod_AtribC := tq.FieldByName('Cod_AtribC').AsString;
  tq.Close;
  tq.Free;
end;

function TTabelaPreco.Excluir;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := dmint.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM TabelaPreco WHERE Tab_codigo=:k_Tab_codigo');
  tq.parambyname('k_Tab_codigo').asstring := strzero(p_Tab_codigo, 3);
  tq.execSQL;
  Result := 1;
  tq.close;
  tq.Free;
end;

procedure TTabelaPreco.Consultar;
begin
  p_q.sql.Clear;
  p_nome := AllTrim(p_nome);
  p_q.Params.Clear;
  //p_q.sql.add('Select * from TabelaPreco where Tab_descricao>=:k_Tab_descricao order by Tab_descricao');
  p_q.sql.add('Select * from TabelaPreco where Tab_descricao like :k_Tab_descricao order by Tab_descricao');
  p_q.parambyname('k_Tab_descricao').asstring := '%' + p_nome + '%';
end;

function TTabelaPreco.Recuperar_Vigente;
var
  tq: TQuery;
  Parametros: TParametros;
begin
  Parametros := TParametros.Create;
  Parametros.Recuperar('1');
  tq := TQuery.Create(dmint);
  tq.databasename := dmInt.Db.DatabaseName;
  tq.active := false;
  tq.sql.clear;
  tq.sql.add('Select (Tab_Codigo) vigente from TabelaPreco where Tab_Vig_Inicio<=:a and ' +
    'Tab_Vig_Termino>=:a');
  tq.params[0].asdatetime := data;
  tq.open;
  if tq.RecordCount > 1 then
    Result := Parametros.Tab_Codigo
  else
    result := tq.fieldbyname('vigente').asstring;
  tq.close;
  tq.free;
  Parametros.Free;
end;

end.
